Water Polygons

Water polygon dataset for Guam.
NOTE: consider drawing the outer edge of polygons the same colour as the fill, then use the completechains dataset to draw the shorelines in a different colour. These are mostly H01 and H02 features (with some exceptions) - see below.
NOTE: there is no cfcc code for these polygons. But, many of them are named and will be generated in the polygon_landmarks dataset.

CREATE TABLE water_polygon AS
 SELECT the_geom,
        pip.module,
        pip.polyid
 FROM poly2,pip
 WHERE pip.module = poly2.module and pip.polyid = poly2.polyid;
 
 
 
INSERT INTO geometry_columns values ('','public','water_polygon','the_geom',2,1,'GEOMETRY');
 
CREATE INDEX  water_poly_idx_module on water_polygon (module);
CREATE INDEX  water_poly_idx_modulepoly on water_polygon (module,polyid);
CREATE INDEX  water_poly_idx_spatial on water_polygon using gist (the_geom gist_geometry_ops);
VACUUM ANALYSE  water_polygon;
 alter table water_polygon add primary key (module,polyid);

Water Lines

!water_l.gif!

The water lines dataset contains river-like features.

CREATE TABLE water_lines AS
         SELECT wkb_geometry as the_geom, fename,
	                 module, tlid,
	                 cfcc,
	                 substring(cfcc from 1 for 1) as cfcc_1,
	 	        substring(cfcc from 2 for 1) as cfcc_2,
                substring(cfcc from 3 for 1) as cfcc_3
         FROM completechain
         WHERE cfcc in (
                'H10','H11','H12','H13', -- rivers
                'H20','H21','H22'      -- canals
         );  

INSERT INTO geometry_columns values ('','public','water_lines','the_geom',2,1,'GEOMETRY');
 
CREATE INDEX  water_lines_idx_module on water_lines (module);
CREATE INDEX  water_lines_idx_moduleid on water_lines (module,tlid);
CREATE INDEX  water_lines_idx_spatial on water_lines using gist (the_geom gist_geometry_ops);
VACUUM ANALYSE  water_lines;

Water Shore Lines

This is a "render"-helper layer; use it to make your polygons look "good".

!water_shore.gif!

CREATE TABLE water_shorelines AS
         SELECT wkb_geometry as the_geom, fename,
                module, tlid,
                cfcc,
                substring(cfcc from 1 for 1) as cfcc_1,
	        substring(cfcc from 2 for 1) as cfcc_2,
                substring(cfcc from 3 for 1) as cfcc_3
          FROM completechain
       WHERE cfcc in (
         'H01','H02',           -- these are "true shorelines"
         'H71','H73',
         'H74','H75', 
         'H80','H81',         --glacial/special water features
         'H30','H31','H32',   --lake outlines
         'H40','H41','H42','H43', -- reservoir outlines
         'H50','H51','H53', -- oceans/bay
         'H60'
       );

INSERT INTO geometry_columns values ('','public','water_shorelines ','the_geom',2,1,'GEOMETRY');
 
CREATE INDEX  water_shorelines_idx_module on water_shorelines (module);
CREATE INDEX  water_shorelines_idx_moduleid on water_shorelines (module,tlid);
CREATE INDEX  water_shorelines_idx_spatial on water_shorelines using gist (the_geom gist_geometry_ops);
VACUUM ANALYSE  water_shorelines ;

Advanced Water Layer

This is for making a "nicer" water layer thats nicer for displaying "zoomed out".

1. for each county, union all the water polygons together
2. union all the water polygons on a state level
3. "explode the table"

Data

The water_polygon table has 1,667,674 polygons with a total of 53,091,679 points.
The water_polygon2 table (unioned-based-on-county) has 1,134,791 polygons and 46,838,495 points.

--county level
CREATE TABLE water_polygon2 AS
  SELECT buffer(collect(the_geom),0) FROM water_polygon GROUP BY module;
--state level
CREATE TABLE water_polygon3 AS
  SELECT buffer(collect(the_geom),0) FROM water_polygon2 GROUP BY  substring(module for 5);

We want "explode" this table so that it has one row per polygon (independent waterbody), instead of just one row per state.

Attachments:

water.gif (image/gif)